MySQL GROUP BY 分组

GROUP BY 子句将查询结果按一个或多个列分组，配合聚合函数进行分组统计。

基本语法

SELECT 列名, 聚合函数(列名)
FROM 表名
GROUP BY 列名;

单列分组

-- 创建示例表
CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    category VARCHAR(30),
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
(1, '手机', '电子产品', 3000),
(2, '电脑', '电子产品', 5000),
(3, '耳机', '电子产品', 500),
(4, '衣服', '服装', 200),
(5, '裤子', '服装', 150);

-- 按类别统计销售额
SELECT category, SUM(amount) AS 总销售额
FROM sales
GROUP BY category;

-- 按类别统计数量和平均值
SELECT category,
       COUNT(*) AS 数量,
       AVG(amount) AS 平均金额,
       SUM(amount) AS 总金额
FROM sales
GROUP BY category;

多列分组

-- 按类别和产品分组
SELECT category, product, SUM(amount) AS 总金额
FROM sales
GROUP BY category, product;

GROUP BY 与 ORDER BY

-- 按类别分组并按总销售额降序
SELECT category, SUM(amount) AS 总销售额
FROM sales
GROUP BY category
ORDER BY 总销售额 DESC;

GROUP BY 与 WHERE

-- WHERE 在 GROUP BY 之前过滤
SELECT category, SUM(amount) AS 总销售额
FROM sales
WHERE amount > 100
GROUP BY category;

WITH ROLLUP 汇总

-- 在分组结果基础上增加汇总行
SELECT category, SUM(amount) AS 总销售额
FROM sales
GROUP BY category WITH ROLLUP;

-- 结果包含每个类别的汇总和总汇总
-- 电子产品 | 8500
-- 服装     | 350
-- NULL     | 8850  (总汇总)

SELECT 中出现的非聚合列必须出现在 GROUP BY 中。

要点总结

• GROUP BY 按列值分组，每组返回一行
• SELECT 中非聚合列必须在 GROUP BY 中出现
• WHERE 在 GROUP BY 之前执行
• WITH ROLLUP 可增加汇总行